package email;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;

import javax.swing.JOptionPane;

public class SqlDBController {

	private static String url;
	private static String username;
	private static String password;
	private static String currentUser;
	
	public SqlDBController(){
		
		if(url == null) url = "jdbc:mysql://localhost:3306";
		if(username == null) username = "root";
		if(password == null) password = "";
		if(currentUser == null) currentUser = "se319test@gmail.com";
		// quick test to see if the database is running and we are connected
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			System.err.println("Couldn't load the driver.");
		}

		Connection con = null;
		try {
			con = DriverManager.getConnection(url, username, password);
			con.close();
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "Could not establish connection to Database. Only displaying inbox. Will not store any new data.");
		}
		// initialize customthreading db along with tables
		initDB();
	}
	
	public static void setCurrentUser(String user){
		currentUser = user;
	}
	
	public static void setUrl(String dbUrl){
		url = dbUrl;
	}
	
	public static void setDbUsername(String dbUser){
		username = dbUser;
	}
	
	public static void setDbPassword(String dbPass){
		password = dbPass;
	}
	
	public HashMap<Long, Thread> loadThreads(){
		String sql = "	SELECT 	t.thread_id, " +
				"			 	t.name, " +
				"			 	m.message_id, " +
				"				m.fromWho, " +
				"				m.recipients, " +
				"				m.date, " +
				"				m.subject, " +
				"				m.body " +
				"		FROM 	threads t, messages m " +
				"		WHERE 	t.user = '"+currentUser+"' AND " +
				"				m.user = '"+currentUser+"' AND " +
				"				t.thread_id = m.thread_id";
		ArrayList<HashMap<String, Object>> tuples = getData(sql);
		
		HashMap<Long, Thread> threads = new HashMap<Long, Thread>();
		for (int i = 0; i < tuples.size(); i++) {
			long thread_id = (Integer) tuples.get(i).get("var1");
			String thread_name = (String) tuples.get(i).get("var2");
			ReceivedMessage msg = new ReceivedMessage();
			long message_id = (Integer) tuples.get(i).get("var3");
			msg.setId(message_id);
			String from = (String) tuples.get(i).get("var4");
			msg.setFrom(from);
			String recipients = (String) tuples.get(i).get("var5");
			msg.setRecipients(recipients);
			String date = (String) tuples.get(i).get("var6");
			msg.setDate(date);
			String subject = (String) tuples.get(i).get("var7");
			msg.setSubject(subject);
			String body = (String) tuples.get(i).get("var8");
			body = body.replace("\\'", "'");
			msg.setBody(body);
			if (!threads.containsKey(thread_id)) {
				ArrayList<ReceivedMessage> emails = new ArrayList<ReceivedMessage>();
				emails.add(msg);
				threads.put(thread_id, new Thread(thread_id, thread_name,
						emails));
			} else {
				Thread t = threads.get(thread_id);
				t.addEmail(msg);
				threads.put(thread_id, t);
			}
		}
		
		return threads;
	}
	
	public boolean addThread(Thread t){
		ArrayList<ReceivedMessage> emails = t.getEmails();
		String sql = "";
		
		sql = "INSERT INTO `customthreading`.`threads` (`user`,`thread_id`,`name`) " +
				  "VALUES ('"+currentUser+"', "+t.getId()+", '"+t.getName()+"')";
		boolean success = storeData(sql);
		
		for(ReceivedMessage email : emails){
			// found an issue with these single quotes
			String body = email.getBody().replace("'", "\\'");
			
			sql = "INSERT INTO `customthreading`.`messages` (`user` ,`message_id` ,`subject` ,`date` ,`body` ,`fromWho` ,`recipients`, `thread_id`) "+
				  "VALUES ('"+currentUser+"', "+email.getId()+", '"+email.getSubject()+"', '"+email.getDate()+"', '"+body+"', '"+email.getFrom()+"', '"+email.getRecipients()+"', "+t.getId()+")";
			
			//ensures that it if it becomes false, it stays false for the return
			success = success && storeData(sql); 
		}
		//not sure if this is how we wanna handle this sort of failure.
		return success;
	}
	
	public boolean unthread(Thread t){
		String sql = "";
		boolean success = false;
		// delete the thread from the table
		sql = "DELETE FROM `customthreading`.`threads` " +
				"WHERE thread_id = " + t.getId() + " and user = '"+currentUser+"'";
		
		success = storeData(sql);
		// delete the messages for this thread
		sql = "DELETE FROM `customthreading`.`messages` " +
			  "WHERE thread_id = "+t.getId()+" and user = '"+currentUser+"'"; 
		success = success && storeData(sql);
		
		return success;
	}
	
	public boolean mergeThread(long[] merged, Thread t){
		String delete = "";
		boolean success = true;
		// delete all the merged threads
		for (long l : merged) {
			delete =  "DELETE FROM `customthreading`.`threads` "
					+ "WHERE thread_id = " + l + " and user = '"+currentUser+"'";
			success = success && storeData(delete);
			
			delete = "DELETE FROM `customthreading`.`messages` " +
					 "WHERE thread_id = " + l + " and user = '"+currentUser+"'";
			success = success && storeData(delete);
		}
		// delete the thread everything was merged to including the messages
		delete =  "DELETE FROM customthreading.threads "
				+ "WHERE thread_id = " + t.getId() + " and user = '"+currentUser+"'";
		success = success && storeData(delete);
		
		delete = "DELETE FROM `customthreading`.`messages` " +
				 "WHERE thread_id = " + t.getId() + " and user = '"+currentUser+"'";
		success = success && storeData(delete);
		// store the new merged thread
		success = success && addThread(t);
		
		return success;
	}
	
	public boolean renameThread(long id, Thread t, String name){
		boolean success = false;
		// update the threads table for name and thread_id
		String update = "UPDATE `customthreading`.`threads` "
					+	"SET name = '" + name + "', thread_id = "+t.getId()
					+	" WHERE thread_id = " + id + " and user = '"+currentUser+"'";
		success = storeData(update);
		// update the messages table for thread_id
		update = "UPDATE `customthreading`.`messages` " +
				 "SET thread_id = " + t.getId() +
				 " WHERE thread_id = " + id + " and user = '"+currentUser+"'";
		
		success = success && storeData(update);
		
		return success;
	}
	
	private void initDB(){
		String sql = "CREATE DATABASE customthreading";
		boolean success = storeData(sql);
		
		if(this.url.endsWith("/")) this.url += "customthreading";
		else this.url += "/customthreading";
		sql = "CREATE TABLE messages(user varchar(200) default 'se319test@gmail.com', " +
				                    "message_id int(11), " +
				                    "subject varchar(1000) not null, " +
				                    "date varchar(75) not null, " +
				                    "body varchar(5000) not null, " +
				                    "fromWho varchar(200) not null, " +
				                    "recipients varchar(5000) not null, " +
				                    "thread_id int(11) references threads, " +
				                    "primary key(user, message_id, thread_id))";
		success = storeData(sql);
		
		sql = "CREATE TABLE threads(user varchar(200)  not null default 'se319test@gmail.com', " +
				                   "thread_id int(11), " +
				                   "name varchar(1000) not null, " +
				                   "primary key(user, thread_id))";
		success = storeData(sql);
		
	}
	
	private ArrayList<HashMap<String, Object>> getData(String sql){
		ArrayList<HashMap<String, Object>> data = new ArrayList<HashMap<String,Object>>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			System.err.println("Unable to load the driver.");
		}
		
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			System.err.println("Unable to get the connection.");
			return data;
		}
		
		try{
			Statement stmt = con.createStatement();
			ResultSet results = stmt.executeQuery(sql);
			ResultSetMetaData meta = results.getMetaData();
			while(results.next()){
				HashMap<String, Object> map = new HashMap<String, Object>();
				for(int i = 1; i <= meta.getColumnCount(); i++){
					Object r = null;
					switch(meta.getColumnType(i)){
					case Types.BOOLEAN:
						r = results.getBoolean(i);
						break;
					case Types.DECIMAL:
					case Types.REAL:
						r = results.getDouble(i);
						break;
					case Types.VARCHAR:
						r = results.getString(i);
						break;
					case Types.INTEGER:
						r = results.getInt(i);
						break;
					case Types.DATE:
						r = results.getDate(i);
						break;
					default:
						r = results.getObject(i);
						break;
					}
					
					map.put("var"+i, r);
				}
				data.add(map);
			}
			results.close();
			stmt.close();
			con.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return data;
	}
	
	
	private boolean storeData(String sql){
		boolean success = false;

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			System.err.println("Couldn't load the driver.");
		}

		Connection con = null;
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			System.err.println("Unable to get the connection.");
			return false;
		}

		try {
			Statement stmt = con.createStatement();
			stmt.execute(sql);
			success = true;
			stmt.close();
			con.close();
		} catch (SQLException e) {
			// Duplicate add or can't update
			success = false;
		}
		
		return success;
	}
}
